'''
--  Case Type:Cursor
--  testpoint:declare声明游标，使用select全量查询，指定游标返回的行，返回结果为空，游标可正常提取数据，提取数据也为空；
--  owner:opentestcase001
--  date:2020-11-03
'''

import unittest
import sys
sys.path.append(sys.path[0] + "/../")
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger

logger = Logger()


class CURSOR_TEST(unittest.TestCase):

    def setUp(self):
        self.sh_primysh = CommonSH('PrimaryDbUser')
        self.constant = Constant()

    def test_cursor_declare(self):
        logger.info("------------------Opengauss_Function_DDL_Cursor_Case0041开始执行-----------------")
        logger.info("======前置条件，创建表，不插入数据======")
        sql_cmd1 = f'''
                    drop table if exists cur_test_41;
                    create table cur_test_41(c_id int,c_num int,c_name varchar(10),c_city varchar(10),c_add varchar(20));
                    '''
        msg1 = self.sh_primysh.execut_db_sql(sql_cmd1)
        logger.info(msg1)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg1)

        logger.info("======指定游标返回的行，返回结果为空，游标可正常提取数据，提取数据也为空======")
        sql_cmd2 = f'''
                    start transaction;
                    declare cursor41 cursor for select * from cur_test_41 order by 1;
                    fetch 1 from cursor41;
                    close cursor41;
                    end;
                    '''

        msg2 = self.sh_primysh.execut_db_sql(sql_cmd2)
        logger.info(msg2)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, msg2)
        self.assertIn(self.constant.DECLEAR_CURSOR_SUCCESS_MSG, msg2)
        self.assertIn('', msg2)
        self.assertIn(self.constant.CLOSE_CURSOR_SUCCESS_MSG, msg2)
        self.assertIn(self.constant.COMMIT_SUCCESS_MSG, msg2)

    def tearDown(self):
        logger.info('======清理环境======')
        sql_cmd3 = f'''
                    drop table cur_test_41 cascade;
                   '''
        msg3 = self.sh_primysh.execut_db_sql(sql_cmd3)
        logger.info(msg3)
        logger.info('---------------Opengauss_Function_DDL_Cursor_Case0041执行结束---------------')


